﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.Data;
using System.Text;
using System.IO;
using System.Net.Mail;
using System.Net;
using System.Data.SqlClient;
using ODP.Model;
using ODP.Enum;
using ODP.DBUtility;


namespace ODP.Helper
{
    public class DALHelper
    {
        #region 默认字符串的长度为150
        /// <summary>
        /// 默认字符串的长度为150
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ci"></param>
        /// <returns></returns>
        public static SqlParameter[] GetParametersByModelInfo<T>(T ci,bool isUpdate = false)
        {
            IList<SqlParameter> paras = new List<SqlParameter>();

            //从泛型ci中得到实体对象   把实体对象转化为PropertyInfo数组
            System.Reflection.PropertyInfo[] pi = ci.GetType().GetProperties();
            string PropertyValue = string.Empty;
            for (int i = 0; i < pi.Length; i++)
            {
                SqlParameter para = new SqlParameter();

                //pi[i].Name   得到实体属性值名称
                string PropertyName = ((System.Reflection.MemberInfo)(pi[i])).Name;
                
                var bugcolumn = BugColumns.Where(p => p.Key == ci.GetType().Name && p.Value == PropertyName).FirstOrDefault();

                if (string.IsNullOrEmpty(bugcolumn.Key) && string.IsNullOrEmpty(bugcolumn.Value))
                {
                    PropertyInfo mi = ci.GetType().GetProperty(PropertyName);//得到属性
                    PropertyValue = Convert.ToString(mi.GetValue(ci, null));
                    if (PropertyValue != null)
                    {
                        //属性SetValue
                        if (mi.PropertyType.ToString().Contains("Int32"))
                        {
                            para.IsNullable = false;
                            para.SqlDbType = SqlDbType.Int;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;

                        }
                        else if (mi.PropertyType.ToString().Contains("Double"))
                        {
                            para.IsNullable = false;
                            para.SqlDbType = SqlDbType.Decimal;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;
                        }
                        else if (mi.PropertyType.ToString().Contains("Decimal"))
                        {
                            para.IsNullable = false;
                            para.SqlDbType = SqlDbType.Decimal;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;
                        }
                        else if (mi.PropertyType.ToString().Contains("DateTime"))
                        {
                            para.IsNullable = false;
                            para.SqlDbType = SqlDbType.DateTime;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;
                        }
                        else
                        {
                            para.IsNullable = true;
                            para.SqlDbType = SqlDbType.NVarChar;
                            para.Size = 150;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;
                        }

                        paras.Add(para);
                    }
                }
            }

            if (isUpdate)
            {
                SqlParameter para = new SqlParameter();
                para.IsNullable = false;
                para.SqlDbType = SqlDbType.DateTime;
                para.ParameterName = "@lastupdatetime";
                para.Value = DateTime.Now;
                paras.Add(para);
            }

            return paras.ToArray();
        }
        #endregion

        #region 可指定某个属性的长度
        /// <summary>
        /// 可指定某个属性的长度
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ci"></param>
        /// <param name="dict"></param>
        /// <returns></returns>
        public static SqlParameter[] GetParametersByModelInfo<T>(T ci,IDictionary<string,int> dict)
        {
            IList<SqlParameter> paras = new List<SqlParameter>();

            //从泛型ci中得到实体对象   把实体对象转化为PropertyInfo数组
            System.Reflection.PropertyInfo[] pi = ci.GetType().GetProperties();
            string PropertyValue = string.Empty;
            for (int i = 0; i < pi.Length; i++)
            {
                SqlParameter para = new SqlParameter();

                //pi[i].Name   得到实体属性值名称
                string PropertyName = ((System.Reflection.MemberInfo)(pi[i])).Name;
                var bugcolumn = BugColumns.Where(p => p.Key == ci.GetType().Name && p.Value == PropertyName).FirstOrDefault();

                if (string.IsNullOrEmpty(bugcolumn.Key) && string.IsNullOrEmpty(bugcolumn.Value))
                {
                    PropertyInfo mi = ci.GetType().GetProperty(PropertyName);//得到属性
                    PropertyValue = Convert.ToString(mi.GetValue(ci, null));
                    if (PropertyValue != null)
                    {
                        //属性SetValue
                        if (mi.PropertyType.ToString().Contains("Int32"))
                        {
                            para.IsNullable = false;
                            para.SqlDbType = SqlDbType.Int;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;

                        }
                        else if (mi.PropertyType.ToString().Contains("Double"))
                        {
                            para.IsNullable = false;
                            para.SqlDbType = SqlDbType.Decimal;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;
                        }
                        else if (mi.PropertyType.ToString().Contains("Decimal"))
                        {
                            para.IsNullable = false;
                            para.SqlDbType = SqlDbType.Decimal;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;
                        }
                        else if (mi.PropertyType.ToString().Contains("DateTime"))
                        {
                            para.IsNullable = false;
                            para.SqlDbType = SqlDbType.DateTime;
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;
                        }
                        else
                        {
                            para.IsNullable = true;
                            para.SqlDbType = SqlDbType.NVarChar;
                            para.Size = dict[PropertyName];
                            para.ParameterName = "@" + PropertyName;
                            para.Value = PropertyValue;
                        }
                    }
                    paras.Add(para);
                }
            }
            return paras.ToArray();
        }
        #endregion

        #region 把DataRow转换为对应的ModelInfo
        /// <summary>
        /// 把DataRow转换为对应的ModelInfo
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <param name="ci"></param>
        /// <returns></returns>
        public static void GeneralModelInfo<T>(DataRow dr,ref T ci)
        {
           

             //从泛型ci中得到实体对象   把实体对象转化为PropertyInfo数组
            System.Reflection.PropertyInfo[] pi = ci.GetType().GetProperties();
            string PropertyValue = string.Empty;
            string ModeName = ci.GetType().Name;
            for (int i = 0; i < pi.Length; i++)
            {
                SqlParameter para = new SqlParameter();

                //pi[i].Name   得到实体属性值名称
                string PropertyName = ((System.Reflection.MemberInfo)(pi[i])).Name;


                var bugcolumn = BugColumns.Where(p => p.Key == ModeName && p.Value == PropertyName).FirstOrDefault();

                if (string.IsNullOrEmpty(bugcolumn.Key) && string.IsNullOrEmpty(bugcolumn.Value))
                {
                    PropertyInfo mi = ci.GetType().GetProperty(PropertyName);//得到属性
                    PropertyValue = Convert.ToString(dr[PropertyName]);

                    if (PropertyValue != null && PropertyValue != "")
                    {
                        if (mi.PropertyType.ToString().Contains("Int32"))
                        {
                            mi.SetValue(ci, Convert.ChangeType(dr[PropertyName], typeof(System.Int32)), null);
                        }
                        else if (mi.PropertyType.ToString().Contains("Double"))
                        {
                            mi.SetValue(ci, Convert.ChangeType(dr[PropertyName], typeof(System.Double)), null);
                        }
                        else if (mi.PropertyType.ToString().Contains("Decimal"))
                        {
                            mi.SetValue(ci, Convert.ChangeType(dr[PropertyName], typeof(System.Decimal)), null);
                        }
                        else if (mi.PropertyType.ToString().Contains("DateTime"))
                        {
                            mi.SetValue(ci, Convert.ChangeType(dr[PropertyName], typeof(System.DateTime)), null);
                        }
                        else
                        {
                            mi.SetValue(ci, Convert.ChangeType(dr[PropertyName], mi.PropertyType), null);
                        }
                    }
                }
            }
        }
        #endregion

        #region 根据指定的modelInfo储存相关数据
        /// <summary>
        /// 根据指定的modelInfo储存相关数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="modelInfo"></param>
        /// <returns></returns>
        public static ExecInfo TSQLForInsertIntoAccess<T>(T modelInfo, FormName FormName)
        {
            ExecInfo ei = new ExecInfo();
            StringBuilder sb = new  StringBuilder();
            StringBuilder attrNamesb = new StringBuilder();

            StringBuilder valuesb = new StringBuilder();

            //从泛型ci中得到实体对象   把实体对象转化为PropertyInfo数组
            System.Reflection.PropertyInfo[] pi = modelInfo.GetType().GetProperties();
            string ColumnValue = string.Empty;
            for (int i = 0; i < pi.Length; i++)
            {
                //pi[i].Name   得到实体属性值名称
                string attrName = ((System.Reflection.MemberInfo)(pi[i])).Name;

                var bugcolumn = BugColumns.Where(p => p.Key == modelInfo.GetType().Name && p.Value == attrName).FirstOrDefault();

                if (string.IsNullOrEmpty(bugcolumn.Key) && string.IsNullOrEmpty(bugcolumn.Value))
                {
                    PropertyInfo mi = modelInfo.GetType().GetProperty(attrName);//得到属性
                    //属性值
                    ColumnValue = Convert.ToString(mi.GetValue(modelInfo, null));

                    if (ColumnValue != "" && attrName != "ID")
                    {
                        attrNamesb.AppendFormat("[{0}],", attrName);

                        valuesb.AppendFormat("@{0},", attrName);

                    }
                }

            }

            if (attrNamesb.Length > 0 && valuesb.Length > 0)
            {
                attrNamesb.Remove(attrNamesb.Length - 1, 1);
                valuesb.Remove(valuesb.Length - 1, 1);

                sb.AppendFormat("insert into [{0}] ({1}) values ({2});", FormName, attrNamesb, valuesb);
            }

            ei.SQL = sb.ToString();
            ei.Paras = GetParametersByModelInfo<T>(modelInfo);

            return ei;

        }
        #endregion

        #region 根据指定的modelInfo储存相关数据
        /// <summary>
        /// 根据指定的modelInfo储存相关数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="modelInfo"></param>
        /// <returns></returns>
        public static ExecInfo TSQLForUpdateAccess<T>(T modelInfo, FormName FormName, string where)
        {
            ExecInfo ei = new ExecInfo();
            StringBuilder sb = new StringBuilder();
            StringBuilder attrNamesb = new StringBuilder();

            StringBuilder valuesb = new StringBuilder();

            //从泛型ci中得到实体对象   把实体对象转化为PropertyInfo数组
            System.Reflection.PropertyInfo[] pi = modelInfo.GetType().GetProperties();
            string ColumnValue = string.Empty;
            for (int i = 0; i < pi.Length; i++)
            {
                //pi[i].Name   得到实体属性值名称
                string attrName = ((System.Reflection.MemberInfo)(pi[i])).Name;

                var bugcolumn = BugColumns.Where(p => p.Key == modelInfo.GetType().Name && p.Value == attrName).FirstOrDefault();

                if (string.IsNullOrEmpty(bugcolumn.Key) && string.IsNullOrEmpty(bugcolumn.Value))
                {
                    PropertyInfo mi = modelInfo.GetType().GetProperty(attrName);//得到属性
                    //属性值
                    ColumnValue = Convert.ToString(mi.GetValue(modelInfo, null));

                    if (ColumnValue != "" && attrName != "ID")
                    {
                        attrNamesb.AppendFormat("{0} = @{0},", attrName);
                    }
                }

            }

            attrNamesb.AppendFormat("{0} = @{0} ", "lastupdatetime");

            if (attrNamesb.Length > 0)
            {
                sb.AppendFormat("update [{0}] set {1} where {2};", FormName, attrNamesb, where);
            }

            ei.SQL = sb.ToString();
            ei.Paras = GetParametersByModelInfo<T>(modelInfo,true);
            return ei;

        }
        #endregion

        #region 执行所有的sql语句
        /// <summary>
        /// 执行所有的sql语句,不需要返回数据的
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="oledbConn"></param>
        /// <returns></returns>
        public static ReturnMessage ExecTSQL(IList<ExecInfo> eiList)
        {
            ReturnMessage rm = new ReturnMessage();

            SqlConnection sqlConn = new SqlConnection(ODP.DBUtility.SqlHelper.ConnectionStringCore);

            if (sqlConn.State == ConnectionState.Closed)
            {
                sqlConn.Open();
            }

            SqlTransaction otran = sqlConn.BeginTransaction(IsolationLevel.ReadUncommitted);

            string tempSql = "";

            try
            {
                foreach (ExecInfo ei in eiList)
                {
                    tempSql = ei.SQL;
                    if (ei.SQL != "")
                    {
                        ODP.DBUtility.SqlHelper.ExecuteNonQuery(otran, CommandType.Text, tempSql, ei.Paras);
                    }
                }

                otran.Commit();
                rm.IsSuccessed = true;
            }
            catch (Exception e)
            {
                otran.Rollback();
                rm.IsSuccessed = false;
                rm.Text = string.Format("数据导入过程中出错!TSQL:{0}", tempSql);
                rm.Exception = e;
            }
            finally
            {
                otran.Dispose();
                sqlConn.Close();
            }

            return rm;
        }
        #endregion

        #region 是否存在指定条件的数据记录
        public static ReturnMessage Exists(FormName FormName, string where)
        {
            ReturnMessage rm = new ReturnMessage();
            rm.IsSuccessed = false;

            string sql = string.Format("select * from [{0}] where {1}", FormName, where);

            DataSet ds = SqlHelper.ExecuteQuery(SqlHelper.ConnectionStringCore, CommandType.Text, sql);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count >0)
            {
                rm.IsSuccessed = true;
            }

            return rm;
        }

        public static ReturnMessage Exists<T>(ref T ci,FormName FormName, string where)
        {
            ReturnMessage rm = new ReturnMessage();
            rm.IsSuccessed = false;

            string sql = string.Format("select * from [{0}] where {1}", FormName, where);

            DataSet ds = SqlHelper.ExecuteQuery(SqlHelper.ConnectionStringCore, CommandType.Text,null);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                GeneralModelInfo<T>(ds.Tables[0].Rows[0], ref ci);

                rm.IsSuccessed = true;
            }

            return rm;
        }
        #endregion

        #region 记录所有的问题栏位
        private static IList<KeyValuePair<string, string>> m_BugColumns = new List<KeyValuePair<string, string>>();

        public static IList<KeyValuePair<string, string>> BugColumns
        {
            get {
                if (m_BugColumns.Count == 0)
                {
                    m_BugColumns.Add(new KeyValuePair<string, string>("UserInfo", "status"));
                }
                return DALHelper.m_BugColumns; 
            }
            set { DALHelper.m_BugColumns = value; }
        }
        
        #endregion
    }
}
